<%@ page contentType="text/html;charset=UTF-8" language="java"%>
<%@ page language="java" trimDirectiveWhitespaces="true"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="org.apache.commons.lang3.StringUtils"%>
<%@ page import="org.apache.commons.lang.StringEscapeUtils"%>
<%@ page import="org.apache.poi.ss.usermodel.*,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.util.HSSFColor,org.apache.poi.ss.util.CellRangeAddress"%>
<%@ page import="cn.dy.workatten.pojo.*"%>
<%@ page import="cn.dy.workatten.dao.*"%>
<%@ page import="cn.dy.base.framework.esb.def.RepMessage"%>
<%@ page import="cn.dy.base.framework.esb.call.imp.JsonServiceProxy"%>
<%@ page import="java.text.DecimalFormat"%>
											
<%
	response.setHeader("Pragma", "No-Cache");
	response.setHeader("Cache-Control", "No-Cache");
	response.setDateHeader("Expires", 0);  
	
	
    
	// 获得参数
    String dept_id = request.getParameter("dept_id");
    String monthStr = request.getParameter("monthStr");
	String searchStr = java.net.URLDecoder.decode(request.getParameter("searchStr"));
     
    RepMessage rep = new RepMessage();
    JsonServiceProxy<RepMessage> service= new JsonServiceProxy<RepMessage>("cn.dy.workatten.service.WorkAttenService",RepMessage.class);
    service.setParam("dept_id", dept_id);
    service.setParam("monthStr", monthStr);
	rep = service.call("getExportWorkAttend");
    Map content=(Map)rep.getContent();
	List<String[]> retList = new ArrayList<String[]>();
	if(content!=null && content.get("result")!=null){
		retList = (List<String[]>)content.get("result");
	}

    
    Date date = new Date();
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String dateString = formatter.format(date);    
  	HSSFWorkbook wb = new HSSFWorkbook();
  	HSSFSheet sheet = wb.createSheet("sheet1");//创建Excel工作表对象    
  	
  	//主标题字体
    HSSFFont titleFont = wb.createFont();
  	titleFont.setFontName("宋体");
  	titleFont.setFontHeightInPoints((short) 28);//字号      
  	titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
    titleFont.setUnderline(HSSFFont.U_SINGLE);
    
    //副标题字体
    HSSFFont subtitleFont = wb.createFont();
    subtitleFont.setFontName("宋体");
    subtitleFont.setFontHeightInPoints((short) 14);//字号      
    subtitleFont.setColor(HSSFColor.GREY_50_PERCENT.index);
    
    //表格头字体
    HSSFFont headFont = wb.createFont();
    headFont.setFontName("宋体");
    headFont.setFontHeightInPoints((short) 9);//字号      
    headFont.setColor(HSSFColor.DARK_BLUE.index);
    headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    
    //表格体字体
    HSSFFont bodyFont = wb.createFont();
    bodyFont.setFontName("宋体");
    bodyFont.setFontHeightInPoints((short) 9);//字号      
    
    //表格体字体（红）
    HSSFFont bodyFontRed = wb.createFont();
    bodyFontRed.setFontName("宋体");
    bodyFontRed.setFontHeightInPoints((short) 9);//字号      
    bodyFontRed.setColor(HSSFColor.RED.index);
    
    //表格体字体（粗）
    HSSFFont bodyFontBold = wb.createFont();
    bodyFontBold.setFontName("宋体");
    bodyFontBold.setFontHeightInPoints((short) 9);//字号      
    bodyFontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  	
  	HSSFCellStyle title_style = wb.createCellStyle();    
    title_style.setFont(titleFont);
  	title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    title_style.setWrapText(true);
    title_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  	title_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框       
  	title_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框       
  	title_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框       
  	title_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    
  	HSSFCellStyle subtitle_style = wb.createCellStyle();    
  	subtitle_style.cloneStyleFrom(title_style);
  	subtitle_style.setFont(subtitleFont);
    
  	HSSFCellStyle head_style = wb.createCellStyle();    
  	head_style.cloneStyleFrom(title_style);
  	head_style.setFont(headFont);
    
  	HSSFCellStyle body_style = wb.createCellStyle();    
  	body_style.cloneStyleFrom(title_style);
  	body_style.setFont(bodyFont);
    
    HSSFCellStyle body_red_style = wb.createCellStyle();    
    body_red_style.cloneStyleFrom(title_style);
    body_red_style.setFont(bodyFontRed);
    
    HSSFCellStyle foot_style = wb.createCellStyle();    
    foot_style.setFont(bodyFontBold);    
    
    String boundState="";
	int year = Integer.parseInt(monthStr.substring(0, 4));
	int month = Integer.parseInt(monthStr.substring(5, 7));
	String titleStr = year + " 年 "+ month +" 月考勤情况表";

	String report_name = year + "年"+ month +"月考勤情况表";
	String report_range = "";  
	
    response.setContentType("application/x-msdownload;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(report_name, "UTF-8") + ".xls");
	
	Calendar calendar = Calendar.getInstance();
	calendar.set(Calendar.YEAR, year - 1);
	calendar.set(Calendar.MONTH, month);
	int monthDayCount = calendar.getActualMaximum(Calendar.DATE); // 这个月份有几天
	
	// 行头
	String[] aColumnHeadTemp = { "序号", "姓名" ,"身份证号", "部门" };
	String[] bColumnHeadTemp = { "上午", "下午", "夜班", "早中晚班", "额外分值（分）", "0点后（时）"};
	
	List alist = new ArrayList(Arrays.asList(aColumnHeadTemp));
	List blist = new ArrayList(Arrays.asList(bColumnHeadTemp));
	for(int i = 1; i <= monthDayCount; i++) {
		alist.add(i  + "日");
		if(i != 1) {
			blist.addAll(Arrays.asList(bColumnHeadTemp));
		}
	}
	String[] aColumnHead = new String[alist.size()];
	alist.toArray(aColumnHead);
	String[] bColumnHead = new String[blist.size()];
	blist.toArray(bColumnHead);
	
	int allColumCount = bColumnHead.length + 4;
	
	
	HSSFRow row = null;
	HSSFCell cell = null;
	int row_num = 3;
	HSSFRow titleRow = sheet.createRow(0); // 标题行
	DecimalFormat df = new DecimalFormat("0.00");
	try {
		// 考勤数据
		for(int i = 0; i < retList.size(); i++) {
			row = sheet.createRow(row_num++);
			cell = row.createCell(0);
			cell.setCellStyle(body_style);
			cell.setCellValue((i+1));// 序号
			String[] oneMonthRecArr = retList.get(i);
			for(int j = 1; j < allColumCount; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneMonthRecArr[j-1]);// 值
			}
		}

		
		
		// 设置标表头
		row = sheet.createRow(1);
		row.setHeightInPoints(9 * 2 + 4);// 字太长，两行高
		sheet.createRow(2);
		// 初始化表头的样式
		for (int i = 0; i < allColumCount; i++) {
			sheet.getRow(0).createCell(i).setCellStyle(title_style);
			sheet.getRow(1).createCell(i).setCellStyle(head_style);
			sheet.getRow(2).createCell(i).setCellStyle(head_style);
		}
		
		for (int i = 0; i < aColumnHead.length; i++) {
			sheet.getRow(0).createCell(i).setCellStyle(title_style);
			
			if(i < 4) {
				cell = row.createCell(i);
			} else if(i >= 4) {
				int cellIndex = i + ((i-4) * 5);
				cell = row.createCell(cellIndex);
			} 
			
			cell.setCellValue(aColumnHead[i]);
			cell.setCellStyle(head_style);
//					sheet.autoSizeColumn(i, true);// 先写数据，后标题，以便一次性调整宽度
//					if (sheet.getColumnWidth(i) < 253 * 256) {// 最大为255*256宽
//						sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);// 汉字调整后总小1-2个字符，256为一个字符宽
//					} else {
//						sheet.setColumnWidth(i, 255 * 256);
//					}
		}
		
		row = sheet.getRow(2);
		for (int i = 0; i < bColumnHead.length; i++) {
			//添加表头内容
			int cellIndex = i + 4;
			HSSFCell headCell = row.createCell(cellIndex);
			headCell.setCellValue(bColumnHead[i]);
			headCell.setCellStyle(head_style);
//		            sheet.autoSizeColumn(i, true);// 先写数据，后标题，以便一次性调整宽度
//					if (sheet.getColumnWidth(i) < 253 * 256) {// 最大为255*256宽
//						sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);// 汉字调整后总小1-2个字符，256为一个字符宽
//					} else {
//						sheet.setColumnWidth(i, 255 * 256);
//					}
		}
		
		
		
		List<Integer[]> list3 = new ArrayList(); //要合并的行和列
		Integer[] array1 = {1, 2, 0, 0}; 
		Integer[] array2 = {1, 2, 1, 1};
		Integer[] array3 = {1, 2, 2, 2};
		Integer[] array4 = {1, 2, 3, 3};
		
		for(int i = 0; i < monthDayCount; i++) {
			int startColumn = 4 + i * 6;
			int endColumn = startColumn + 5;
			Integer[] array6 = {1, 1, startColumn, endColumn};
			list3.add(array6);
		}
		
		
		
		list3.add(array1);
		list3.add(array2);
		list3.add(array3);
		list3.add(array4);
		
		for (Integer[] arr : list3) {//合并表头
			sheet.addMergedRegion(new CellRangeAddress(arr[0], arr[1], arr[2], arr[3])); 
		}
		
		
		// 设置标题
		titleRow.setHeightInPoints(28 + 4);
		cell = titleRow.getCell(0);// 创建Excel工作表指定行的单元格
		cell.setCellStyle(title_style);
		cell.setCellValue(titleStr); // 设置Excel工作表的值
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (allColumCount - 1)));
		} catch (Exception e1) {
			e1.printStackTrace();
			System.out.println("导出excel异常:"+e1);
		}
	    out.clear();//清空前面的空格，否则会报错
	    out=pageContext.pushBody();
	    wb.write(response.getOutputStream());//写入输出流中
%>


<!DOCTYPE html>
<html class="no-js">

<head>
	<title>导出</title>
</head>

<body class="mainbody">

	<form method="post" id="infoForm" name="infoForm" enctype="multipart/form- data">
	   
	</form>

</body>

</html>